﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using DBUtility;
namespace OracleDAL.Room
{
    /// <summary>
    /// 数据访问类ROOM_LIST。
    /// </summary>
    public class ROOM_LIST
    {
        public ROOM_LIST()
        { }
        #region  成员方法

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string ROOMID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ROOM_LIST");
            strSql.Append(" where ROOMID=:ROOMID ");
            OracleParameter[] parameters = {
					new OracleParameter(":ROOMID", OracleType.VarChar,50)};
            parameters[0].Value = ROOMID;

            return OracleHelper.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(Model.Room.ROOM_LIST model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into ROOM_LIST(");
			strSql.Append("ROOMID,ROOMNAME,BELONGAREA,REMARK,BACKGROUNDIMAGE,ROOMIMAGE,ADDRESS,LAT,LNG,NODEID,STATIONIP,MODULEPATH,ACQTIME,IGNORESTATUS,STATIONSTATUS,UPDATETIME,ROOMTYPE,DIRECTION)");
			strSql.Append(" values (");
			strSql.Append(":ROOMID,:ROOMNAME,:BELONGAREA,:REMARK,:BACKGROUNDIMAGE,:ROOMIMAGE,:ADDRESS,:LAT,:LNG,:NODEID,:STATIONIP,:MODULEPATH,:ACQTIME,:IGNORESTATUS,:STATIONSTATUS,:UPDATETIME,:ROOMTYPE,:DIRECTION)");
			OracleParameter[] parameters = {
					new OracleParameter(":ROOMID", OracleType.VarChar,25),
					new OracleParameter(":ROOMNAME", OracleType.VarChar,50),
					new OracleParameter(":BELONGAREA", OracleType.VarChar,25),
					new OracleParameter(":REMARK", OracleType.VarChar,500),
					new OracleParameter(":BACKGROUNDIMAGE", OracleType.VarChar,100),
					new OracleParameter(":ROOMIMAGE", OracleType.VarChar,100),
					new OracleParameter(":ADDRESS", OracleType.VarChar,200),
					new OracleParameter(":LAT", OracleType.Float,22),
					new OracleParameter(":LNG", OracleType.Float,22),
					new OracleParameter(":NODEID", OracleType.VarChar,64),
					new OracleParameter(":STATIONIP", OracleType.VarChar,64),
					new OracleParameter(":MODULEPATH", OracleType.VarChar,64),
					new OracleParameter(":ACQTIME", OracleType.Number,4),
					new OracleParameter(":IGNORESTATUS", OracleType.Number,4),
					new OracleParameter(":STATIONSTATUS", OracleType.VarChar,10),
					new OracleParameter(":UPDATETIME", OracleType.DateTime),
					new OracleParameter(":ROOMTYPE", OracleType.VarChar,64),
					new OracleParameter(":DIRECTION", OracleType.VarChar,2)};
			parameters[0].Value = model.ROOMID;
			parameters[1].Value = model.ROOMNAME;
			parameters[2].Value = model.BELONGAREA;
			parameters[3].Value = model.REMARK;
			parameters[4].Value = model.BACKGROUNDIMAGE;
			parameters[5].Value = model.ROOMIMAGE;
			parameters[6].Value = model.ADDRESS;
			parameters[7].Value = model.LAT;
			parameters[8].Value = model.LNG;
			parameters[9].Value = model.NODEID;
			parameters[10].Value = model.STATIONIP;
			parameters[11].Value = model.MODULEPATH;
			parameters[12].Value = model.ACQTIME;
			parameters[13].Value = model.IGNORESTATUS;
			parameters[14].Value = model.STATIONSTATUS;
			parameters[15].Value = model.UPDATETIME;
			parameters[16].Value = model.ROOMTYPE;
			parameters[17].Value = model.DIRECTION;

			OracleHelper.ExecuteSql(strSql.ToString(),parameters);
		}
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(Model.Room.ROOM_LIST model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ROOM_LIST set ");
            strSql.Append("ROOMID=:ROOMID,");
            strSql.Append("ROOMNAME=:ROOMNAME,");
            strSql.Append("BELONGAREA=:BELONGAREA,");
            strSql.Append("REMARK=:REMARK,");
            strSql.Append("BACKGROUNDIMAGE=:BACKGROUNDIMAGE,");
            strSql.Append("ROOMIMAGE=:ROOMIMAGE,");
            strSql.Append("ADDRESS=:ADDRESS,");
            strSql.Append("LAT=:LAT,");
            strSql.Append("LNG=:LNG,");
            strSql.Append("NODEID=:NODEID,");
            strSql.Append("STATIONIP=:STATIONIP,");
            strSql.Append("MODULEPATH=:MODULEPATH,");
            strSql.Append("ACQTIME=:ACQTIME,");
            strSql.Append("IGNORESTATUS=:IGNORESTATUS,");
            strSql.Append("STATIONSTATUS=:STATIONSTATUS,");
            strSql.Append("UPDATETIME=:UPDATETIME,");
            strSql.Append("ROOMTYPE=:ROOMTYPE,");
            strSql.Append("DIRECTION=:DIRECTION");
            strSql.Append(" where ROOMID=:ROOMID and ROOMNAME=:ROOMNAME and BELONGAREA=:BELONGAREA and REMARK=:REMARK and BACKGROUNDIMAGE=:BACKGROUNDIMAGE and ROOMIMAGE=:ROOMIMAGE and ADDRESS=:ADDRESS and LAT=:LAT and LNG=:LNG and NODEID=:NODEID and STATIONIP=:STATIONIP and MODULEPATH=:MODULEPATH and ACQTIME=:ACQTIME and IGNORESTATUS=:IGNORESTATUS and STATIONSTATUS=:STATIONSTATUS and UPDATETIME=:UPDATETIME and ROOMTYPE=:ROOMTYPE and DIRECTION=:DIRECTION ");
            OracleParameter[] parameters = {
					new OracleParameter(":ROOMID", OracleType.VarChar,25),
					new OracleParameter(":ROOMNAME", OracleType.VarChar,50),
					new OracleParameter(":BELONGAREA", OracleType.VarChar,25),
					new OracleParameter(":REMARK", OracleType.VarChar,500),
					new OracleParameter(":BACKGROUNDIMAGE", OracleType.VarChar,100),
					new OracleParameter(":ROOMIMAGE", OracleType.VarChar,100),
					new OracleParameter(":ADDRESS", OracleType.VarChar,200),
					new OracleParameter(":LAT", OracleType.Float ,22),
					new OracleParameter(":LNG", OracleType.Float,22),
					new OracleParameter(":NODEID", OracleType.VarChar,64),
					new OracleParameter(":STATIONIP", OracleType.VarChar,64),
					new OracleParameter(":MODULEPATH", OracleType.VarChar,64),
					new OracleParameter(":ACQTIME", OracleType.Number,4),
					new OracleParameter(":IGNORESTATUS", OracleType.Number,4),
					new OracleParameter(":STATIONSTATUS", OracleType.VarChar,10),
					new OracleParameter(":UPDATETIME", OracleType.DateTime),
					new OracleParameter(":ROOMTYPE", OracleType.VarChar,64),
					new OracleParameter(":DIRECTION", OracleType.VarChar,2)};
            parameters[0].Value = model.ROOMID;
            parameters[1].Value = model.ROOMNAME;
            parameters[2].Value = model.BELONGAREA;
            parameters[3].Value = model.REMARK;
            parameters[4].Value = model.BACKGROUNDIMAGE;
            parameters[5].Value = model.ROOMIMAGE;
            parameters[6].Value = model.ADDRESS;
            parameters[7].Value = model.LAT;
            parameters[8].Value = model.LNG;
            parameters[9].Value = model.NODEID;
            parameters[10].Value = model.STATIONIP;
            parameters[11].Value = model.MODULEPATH;
            parameters[12].Value = model.ACQTIME;
            parameters[13].Value = model.IGNORESTATUS;
            parameters[14].Value = model.STATIONSTATUS;
            parameters[15].Value = model.UPDATETIME;
            parameters[16].Value = model.ROOMTYPE;
            parameters[17].Value = model.DIRECTION;

            OracleHelper.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(string ROOMID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ROOM_LIST ");
            strSql.Append(" where ROOMID=:ROOMID ");
            OracleParameter[] parameters = {
					new OracleParameter(":ROOMID", OracleType.VarChar,50)};
            parameters[0].Value = ROOMID;

            OracleHelper.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.Room.ROOM_LIST GetModel(string ROOMID)
        {

            StringBuilder strSql = new StringBuilder();
            //strSql.Append("select RL.ROOMID,RL.ROOMNAME,RL.STATIONIP,(select COLLECTID from ENV_COLLECT where BELONGROOM=RL.roomid) AS COLLECTID,");
            //strSql.Append(" (select CODEVALUE  from CODE where RL.BELONGAREA=CODEID) AS BELONGAREANAME,RL.BELONGAREA,RL.REMARK,RL.BACKGROUNDIMAGE,");
            //strSql.Append(" RL.ROOMIMAGE,RL.ADDRESS,RL.LAT,RL.LNG from ROOM_LIST RL ");
            //strSql.Append(" where RL.ROOMID=:ROOMID");
            //strSql.Append(" order by RL.BELONGAREA,RL.ROOMID");
            strSql.Append("select RL.ROOMID,RL.ROOMNAME,BELONGAREA,(select CODEVALUE  from CODE where RL.BELONGAREA=CODEID) AS BELONGAREANAME,RL.REMARK,RL.BACKGROUNDIMAGE,RL.ROOMIMAGE,");
            strSql.Append(" RL.ADDRESS,RL.LAT,RL.LNG,RL.NODEID,RL.STATIONIP,RL.MODULEPATH,RL.ACQTIME,RL.IGNORESTATUS,RL.STATIONSTATUS,");
            strSql.Append(" RL.UPDATETIME,RL.ROOMTYPE,RL.DIRECTION ");
            strSql.Append(" from ROOM_LIST RL ");
            strSql.Append(" where RL.ROOMID=:ROOMID");
            OracleParameter[] parameters = {
					new OracleParameter(":ROOMID", OracleType.VarChar,50)};
            parameters[0].Value = ROOMID;

            Model.Room.ROOM_LIST model = new Model.Room.ROOM_LIST();
            DataSet ds = OracleHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.ROOMID = ds.Tables[0].Rows[0]["ROOMID"].ToString();
                model.ROOMNAME = ds.Tables[0].Rows[0]["ROOMNAME"].ToString();
                //model.COLLECTID = ds.Tables[0].Rows[0]["COLLECTID"].ToString();
                model.BELONGAREA = ds.Tables[0].Rows[0]["BELONGAREANAME"].ToString();
                //model.BELONGAREA = ds.Tables[0].Rows[0]["BELONGAREA"].ToString();
                model.REMARK = ds.Tables[0].Rows[0]["REMARK"].ToString();
                model.BACKGROUNDIMAGE = ds.Tables[0].Rows[0]["BACKGROUNDIMAGE"].ToString();
                model.ROOMIMAGE = ds.Tables[0].Rows[0]["ROOMIMAGE"].ToString();
                model.ADDRESS = ds.Tables[0].Rows[0]["ADDRESS"].ToString();
                model.STATIONIP = ds.Tables[0].Rows[0]["STATIONIP"].ToString();
                model.DIRECTION = ds.Tables[0].Rows[0]["DIRECTION"].ToString();
                if (ds.Tables[0].Rows[0]["LAT"].ToString() != "")
                {
                    model.LAT = decimal.Parse(ds.Tables[0].Rows[0]["LAT"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LNG"].ToString() != "")
                {
                    model.LNG = decimal.Parse(ds.Tables[0].Rows[0]["LNG"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select RL.ROOMID,RL.ROOMNAME,(select CODEVALUE  from CODE where RL.BELONGAREA=CODEID) AS BELONGAREA,RL.REMARK,RL.BACKGROUNDIMAGE,RL.ROOMIMAGE,");
            strSql.Append(" RL.ADDRESS,RL.LAT,RL.LNG,RL.NODEID,RL.STATIONIP,RL.MODULEPATH,RL.ACQTIME,RL.IGNORESTATUS,RL.STATIONSTATUS,");
            strSql.Append(" RL.UPDATETIME,RL.ROOMTYPE,RL.DIRECTION ");
            strSql.Append(" from ROOM_LIST RL ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by RL.BELONGAREA,to_number(RL.ROOMID)");
            return OracleHelper.Query(strSql.ToString());
        }
      /// <summary>
      /// 获取机房列表数据
      /// </summary>
      /// <param name="ID">节点ID</param>
      /// <param name="NODETYPE">节点类型(NODE表示获取区域列表，LEAF获取区域下机房列表)</param>
      /// <returns></returns>
        public DataSet GetRoomList(string ID,string NODETYPE)
        {
            StringBuilder strSql = new StringBuilder();
            switch (NODETYPE.ToUpper()) { 
                case "NODE":
                    strSql.Append("select CODEID as ID,CODEVALUE as TEXT,'false' as LEAF from CODE where CODEID like '" + ID + "__'");
                    break;
                case "LEAF":
                    strSql.Append("select ROOMID as ID,ROOMNAME as TEXT,'false' as LEAF from ROOM_LIST where BELONGAREA = '" + ID + "'");                   
                    break;
                default :
                    DataSet ds = new Common.ResourceStatus().GetEquipList("", ID, "");
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        return ds;
                    }
                    return null;
                    break;
            }
            strSql.Append("  order by ID,TEXT");
            return OracleHelper.Query(strSql.ToString());
        }
        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            OracleParameter[] parameters = {
                    new OracleParameter(":tblName", OracleType.VarChar, 255),
                    new OracleParameter(":fldName", OracleType.VarChar, 255),
                    new OracleParameter(":PageSize", OracleType.Number),
                    new OracleParameter(":PageIndex", OracleType.Number),
                    new OracleParameter(":IsReCount", OracleType.Clob),
                    new OracleParameter(":OrderType", OracleType.Clob),
                    new OracleParameter(":strWhere", OracleType.VarChar,1000),
                    };
            parameters[0].Value = "ROOM_LIST";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  成员方法
    }
}

